﻿package component.tree.sql;

import component.tree.entity.Tree;
import component.tree.model.OrgTreeModel;
import component.tree.model.TreeModel;
import component.tree.util.StringUtil;

public class DB2TreeSql implements ITreeSql {
	protected String getFieldName(String entityFieldName) {
		String fieldName = "f";
		for (int i = 0; i < entityFieldName.length(); i++) {
			char c = entityFieldName.charAt(i);
			if (Character.isUpperCase(c)) {
				fieldName = fieldName + "_" + c;
			} else {
				fieldName = fieldName + c;
			}
		}

		return fieldName;
	}
	
	/**
	 * 查询的字段有：id,parentId,name,type,code,otherValue
	 * 格式：字段名 as 类的属性名
	 * @param treeModel
	 * @return
	 */
	private String getSelectSql(TreeModel treeModel){
		StringBuffer sb = new StringBuffer(1000);

		sb.append("select ")
		  .append(treeModel.getFidName()).append(" as ").append(getFieldName(treeModel.getIdName())).append(" , ")
		  .append(treeModel.getFparentIdName()).append(" as ").append(getFieldName(treeModel.getParentIdName())).append(" , ")
		  .append(treeModel.getFnameName()).append(" as ").append(getFieldName(treeModel.getNameName())).append(" , ")
		  .append(treeModel.getFtypeName()).append(" as ").append(getFieldName(treeModel.getTypeName())).append(" , ")
		  .append(treeModel.getFcodeName()).append(" as ").append(getFieldName(treeModel.getCodeName())).append(" , ")
		  .append(treeModel.getFotherValueName()).append(" as ").append(getFieldName(treeModel.getOtherValueName())).append(" \n")
		  .append("from ").append(treeModel.getTableName()).append(" as treeTable \n")
		  .append("where 1=1 ").append(treeModel.getDefaultCondition()).append(" \n");
		  if (!StringUtil.isNull(treeModel.getCondition())) {
			sb.append(treeModel.getCondition());
		  }
		  
		return sb.toString();
	}
	
	/**
	 * 查询的字段有：id,parentId,name,type,code,otherValue
	 * 格式：表的别名.字段名 as 类的属性名
	 * 用于递归查询
	 * @param treeModel
	 * @return
	 */
	private String getSelectSqlHasAlias(TreeModel treeModel){
		String temp = "treeTable.";
		if(treeModel.getFtypeName().length()>5){
			temp = "";
		}
		StringBuffer sb = new StringBuffer(1000);
		
		sb.append("select ")
		  .append("treeTable.").append(treeModel.getFidName()).append(" as ").append(getFieldName(treeModel.getIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFparentIdName()).append(" as ").append(getFieldName(treeModel.getParentIdName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFnameName()).append(" as ").append(getFieldName(treeModel.getNameName())).append(" , ")
		  .append(temp).append(treeModel.getFtypeName()).append(" as ").append(getFieldName(treeModel.getTypeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFcodeName()).append(" as ").append(getFieldName(treeModel.getCodeName())).append(" , ")
		  .append("treeTable.").append(treeModel.getFotherValueName()).append(" as ").append(getFieldName(treeModel.getOtherValueName())).append(" \n")
		  .append("from ").append(treeModel.getTableName()).append(" as treeTable,temp \n")
		  .append("where treeTable." + treeModel.getFidName() + "=temp."+getFieldName(treeModel.getParentIdName()));
		
		return sb.toString();
	}
	
	/**
	 * 获取所有查询的字段，以逗号隔开
	 * 查询的字段有：id,parentId,name,type,code,otherValue
	 * @param treeModel
	 * @return
	 */
	protected String getAllField(TreeModel treeModel){
		StringBuffer sb = new StringBuffer(20);
		
		String idName = treeModel.getIdName();
		String idFieldName = getFieldName(idName);
		sb.append(idFieldName);
		sb.append(",");
		
		String parentIdName = treeModel.getParentIdName();
		String parentIdFieldName = getFieldName(parentIdName);
		sb.append(parentIdFieldName);
		sb.append(",");
		
		String nameName = treeModel.getNameName();
		String nameFieldName = getFieldName(nameName);
		sb.append(nameFieldName);
		sb.append(",");
		
		String typeName = treeModel.getTypeName();
		String typeFieldName = getFieldName(typeName);
		sb.append(typeFieldName);
		sb.append(",");
		
		String codeName = treeModel.getCodeName();
		String codeFieldName = getFieldName(codeName);
		sb.append(codeFieldName);
		sb.append(",");
		
		String otherValueName = treeModel.getOtherValueName();
		String otherValueFieldName = getFieldName(otherValueName);
		sb.append(otherValueFieldName);
		
		return sb.toString();
	}
	
	//根据父id查询子节点
	public String getChildSql(String parentId,TreeModel treeModel){
		StringBuffer sb = new StringBuffer(1000);
		sb.append(getSelectSql(treeModel));
		sb.append(" and " + treeModel.getFparentIdName() + "='" + parentId + "'");
		return sb.toString();
	}
	
	public String getTreeSql(TreeModel treeModel,boolean isQueryFirstLevel){
		StringBuffer sb = new StringBuffer(100);
		sb.append("with temp(").append(getAllField(treeModel)).append(") as ( \n");
		sb.append(getSelectSql(treeModel)).append("\n");
		
		sb.append("\n");
		sb.append("union all \n");
		sb.append("\n");
		
		sb.append(getSelectSqlHasAlias(treeModel)).append("\n");
		sb.append(") \n");
		
		sb.append("select distinct * from temp \n");
		if(isQueryFirstLevel){//只查询第一层
			sb.append(" where " + getFieldName(treeModel.getParentIdName()) + "='" + Tree.ROOT_ID + "' ");
		}
		return sb.toString();
	}
	
	public static void main(String[] args) {
		DB2TreeSql obj = new DB2TreeSql();
		TreeModel treeModel = new OrgTreeModel();
		System.out.println(obj.getTreeSql(treeModel, false));
	}

}
